﻿using System;

namespace Dapper.Sharding
{
    internal class SqlServerUnion : IUnion
    {
        public SqlServerUnion(IDatabase db) : base(db)
        {
        }

        public SqlServerUnion(IDatabase db, string sql) : base(db, sql)
        {
        }

        public override string Sql()
        {
            if (string.IsNullOrEmpty(returnFields))
            {
                returnFields = "*";
            }

            if (take == 0)
            {
                return $"SELECT{distinct} {returnFields} FROM ({sqlTable}) AS UTable{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
            }
            else
            {
                if (skip == 0) //第一页,使用Top语句
                {
                    return $"SELECT{distinct} TOP {take} {returnFields} FROM ({sqlTable}) AS UTable{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
                }
                else
                {
                    if (db.DbVersion == DataBaseVersion.SqlServer2012)
                    {
                        if (string.IsNullOrEmpty(sqlOrderBy))
                        {
                            throw new Exception("sqlserver IUnion must set OrderBy==>for OFFSET [skip] ROWS FETCH NEXT [take] ROWS ONLY");
                        }

                        return $"SELECT{distinct} {returnFields} FROM ({sqlTable}) AS UTable{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
                    }
                    else
                    {
                        if (string.IsNullOrEmpty(sqlOrderBy))
                        {
                            throw new Exception("sqlserver IUnion must set OrderBy==>for ROW_NUMBER OVER(???)");
                        }

                        //使用ROW_NUMBER()
                        return $"SELECT{distinct} {returnFields} FROM (SELECT ROW_NUMBER() OVER({sqlOrderBy.Trim()}) AS Row_Number,{returnFields} FROM ({sqlTable}) AS UTable{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}) A WHERE Row_Number BETWEEN {skip + 1} AND {skip + take}";
                    }
                }

            }
        }

        public override string SqlCount()
        {
            return $"SELECT COUNT(1) FROM ({sqlTable}) AS UTable{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #region aggregate

        public override string SqlExists()
        {
            return $"SELECT TOP 1 1 FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
        }

        public override string SqlMin(string field)
        {
            return $"SELECT MIN({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlMax(string field)
        {
            return $"SELECT MAX({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlSum(string field)
        {
            return $"SELECT{distinct} SUM({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlAvg(string field)
        {
            return $"SELECT{distinct} AVG({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #endregion
    }
}
